﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_Family
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Requisition
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetRequisition(string requisitionKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT * FROM SRM_Requisition_Tbl WHERE RequisitionKey = {0};", requisitionKey);

                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetRequisition

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static string GetUnsolicitedRequisitionKey()
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT RequisitionKey FROM SRM_Requisition_Tbl  where JobTitleFreeText = 'Unsolicited'");

                da.Fill(ds);

                dtResult = ds.Tables[0];

                if (dtResult.Rows.Count > 0)
                    return dtResult.Rows[0]["RequisitionKey"].ToString();
            }

            return string.Empty;
        } // public static DataTable GetRequisition

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataSet SearchRequisition(string strApplicant, string strApplicantStatus, string strRequisitionName, string strCompanyName,
            string strLocationName, DateTime dtPostingDateFrom, DateTime dtPostingDateTo, string strStatusCode, string strLocalFlag)
        {
            DataSet ds = new DataSet();
            //DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "SRM_SEARCHREQUISITION_SP";
                cmd.Parameters.AddWithValue("ApplicantName", strApplicant);
                cmd.Parameters.AddWithValue("ApplicantStatus", strApplicantStatus);
                cmd.Parameters.AddWithValue("RequisitionName", strRequisitionName);
                cmd.Parameters.AddWithValue("CompanyName", strCompanyName);

                cmd.Parameters.AddWithValue("LocationName", strLocationName);
                cmd.Parameters.AddWithValue("PostingDateFrom", dtPostingDateFrom);
                cmd.Parameters.AddWithValue("PostingDateTo", dtPostingDateTo);

                cmd.Parameters.AddWithValue("StatusCode", strStatusCode);
                cmd.Parameters.AddWithValue("LocalFlag", strLocalFlag);
        
                da.Fill(ds);

                //dtResult = ds.Tables[0];
            }

            return ds;
        } // public static DataTable GetRequisition



        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertApplicant(string applicantKey, string requisitionKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"IF NOT EXISTS (SELECT * FROM SRM_RequisitionApplicant_Tbl WHERE ApplicantKey = @ApplicantKey AND RequisitionKey = @RequisitionKey) 
INSERT INTO SRM_RequisitionApplicant_Tbl(RequisitionKey, ApplicantKey, ApplicantStatus, ApplicationDate, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy) 
VALUES (@RequisitionKey,@ApplicantKey,'New',GETDATE(),GETDATE(),'SYSTEM',GETDATE(),'SYSTEM');";

                cmd.Parameters.AddWithValue("@RequisitionKey", Help.GetValueOrDBNull(requisitionKey));
                cmd.Parameters.AddWithValue("@ApplicantKey", Help.GetValueOrDBNull(applicantKey));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertApplicant


        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertFamily(string applicantKey, 
            string memberName, string relationship, string birthDate, string nationality, string familyEmployer, 
            string occupation, string emergencyContact, string contactNo, string timestamp, 
            string surname, string birthPlace, string gender, string idNumber, string residencyType)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT SRM_ApplicantFamilyMembers_Tbl (ApplicantKey,MemberName,Relationship,BirthDate,Nationality,FamilyEmployer,Occupation,EmergencyContact,ContactNo,Timestamp,Surname,BirthPlace,Gender,IdNumber,ResidencyType) VALUES (@ApplicantKey,@MemberName,@Relationship,@BirthDate,@Nationality,@FamilyEmployer,@Occupation,@EmergencyContact,@ContactNo,@Timestamp,@Surname,@BirthPlace,@Gender,@IdNumber,@ResidencyType);";
                
//@ApplicantKey,
//@MemberName,
//@Relationship,
//@BirthDate,
//@Nationality,
//@FamilyEmployer,
//@Occupation,
                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@MemberName", Help.GetValueOrDBNull(memberName));
                cmd.Parameters.AddWithValue("@Relationship", Help.GetValueOrDBNull(relationship));
                cmd.Parameters.AddWithValue("@BirthDate", Help.GetValueOrDBNull(birthDate));
                cmd.Parameters.AddWithValue("@Nationality", Help.GetValueOrDBNull(nationality));
                cmd.Parameters.AddWithValue("@FamilyEmployer", Help.GetValueOrDBNull(familyEmployer));
                cmd.Parameters.AddWithValue("@Occupation", Help.GetValueOrDBNull(occupation));

//@EmergencyContact,
//@ContactNo,
//@Timestamp,
                cmd.Parameters.AddWithValue("@EmergencyContact", Help.GetValueOrDBNull(emergencyContact));
                cmd.Parameters.AddWithValue("@ContactNo", Help.GetValueOrDBNull(contactNo));
                cmd.Parameters.AddWithValue("@Timestamp", Help.GetValueOrDBNull(timestamp));
//@Surname,
//@Gender,
//@IdNumber,
//@ResidencyType string surname, string gender, string idNumber, string residencyType)
                cmd.Parameters.AddWithValue("@Surname", Help.GetValueOrDBNull(surname));
                cmd.Parameters.AddWithValue("@BirthPlace", Help.GetValueOrDBNull(birthPlace));
                cmd.Parameters.AddWithValue("@Gender", Help.GetValueOrDBNull(gender));
                cmd.Parameters.AddWithValue("@IdNumber", Help.GetValueOrDBNull(idNumber));
                cmd.Parameters.AddWithValue("@ResidencyType", Help.GetValueOrDBNull(residencyType));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteFamily(string familyMemberKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantFamilyMembers_Tbl WHERE FamilyMemberKey = @FamilyMemberKey;";
                cmd.Parameters.AddWithValue("@FamilyMemberKey", familyMemberKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)

        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateFamily(string familyMemberKey,
            string memberName, string relationship, string birthDate, string nationality, string familyEmployer,
            string occupation, string emergencyContact, string contactNo, string timestamp,
            string surname, string birthPlace, string gender, string idNumber, string residencyType)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantFamilyMembers_Tbl 
SET     MemberName = @MemberName, 
        Relationship = @Relationship,
        BirthDate = @BirthDate,
        Nationality = @Nationality,
        FamilyEmployer = @FamilyEmployer,
        Occupation = @Occupation,
        EmergencyContact = @EmergencyContact,
        ContactNo = @ContactNo,
        Timestamp = @Timestamp,
        Surname = @Surname,
        BirthPlace = @BirthPlace, 
        Gender = @Gender,
        IdNumber = @IdNumber,
        ResidencyType = @ResidencyType
WHERE   FamilyMemberKey = @FamilyMemberKey;";

                cmd.Parameters.AddWithValue("@FamilyMemberKey", familyMemberKey);
                //@MemberName,
                //@Relationship,
                //@BirthDate,
                //@Nationality,
                //@FamilyEmployer,
                
                cmd.Parameters.AddWithValue("@MemberName", Help.GetValueOrDBNull(memberName));
                cmd.Parameters.AddWithValue("@Relationship", Help.GetValueOrDBNull(relationship));
                cmd.Parameters.AddWithValue("@BirthDate", Help.GetValueOrDBNull(birthDate));
                cmd.Parameters.AddWithValue("@Nationality", Help.GetValueOrDBNull(nationality));
                cmd.Parameters.AddWithValue("@FamilyEmployer", Help.GetValueOrDBNull(familyEmployer));

                //@Occupation,
                //@EmergencyContact,
                //@ContactNo,
                //@Timestamp,
                cmd.Parameters.AddWithValue("@Occupation", Help.GetValueOrDBNull(occupation));
                cmd.Parameters.AddWithValue("@EmergencyContact", Help.GetValueOrDBNull(emergencyContact));
                cmd.Parameters.AddWithValue("@ContactNo", Help.GetValueOrDBNull(contactNo));
                cmd.Parameters.AddWithValue("@Timestamp", Help.GetValueOrDBNull(timestamp));
                //@Surname,
                //@Gender,
                //@IdNumber,
                //@ResidencyType string surname, string gender, string idNumber, string residencyType)
                cmd.Parameters.AddWithValue("@Surname", Help.GetValueOrDBNull(surname));
                cmd.Parameters.AddWithValue("@BirthPlace", Help.GetValueOrDBNull(birthPlace));
                cmd.Parameters.AddWithValue("@Gender", Help.GetValueOrDBNull(gender));
                cmd.Parameters.AddWithValue("@IdNumber", Help.GetValueOrDBNull(idNumber));
                cmd.Parameters.AddWithValue("@ResidencyType", Help.GetValueOrDBNull(residencyType));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void UpdateFamily(string FamilyKey,

    }
}